import sys
from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QPushButton, QFileDialog, QMessageBox, QListWidget, QProgressDialog
from PyQt5.QtCore import Qt, pyqtSlot
import pandas as pd
import os
import time

class MainWindow(QWidget):
    def __init__(self):
        super().__init__()
        self.initUI()

    def initUI(self):
        self.setWindowTitle("Excel文件处理工具")
        self.setGeometry(100, 100, 400, 300)

        self.layout = QVBoxLayout()

        # 添加选择画面表按钮
        self.select_frame_button = QPushButton("选择画面表")
        self.select_frame_button.clicked.connect(self.select_frame_files)
        self.layout.addWidget(self.select_frame_button)

        # 添加选择点位表按钮
        self.select_point_button = QPushButton("选择点位表")
        self.select_point_button.clicked.connect(self.select_point_files)
        self.layout.addWidget(self.select_point_button)

        # 添加取消选择按钮
        self.cancel_selection_button = QPushButton("取消选择")
        self.cancel_selection_button.clicked.connect(self.cancel_selection)
        self.layout.addWidget(self.cancel_selection_button)

        # 添加显示已选择文件的列表框
        self.selected_files_display = QListWidget()
        self.layout.addWidget(self.selected_files_display)

        # 添加生成最终文件按钮
        self.generate_final_button = QPushButton("生成最终文件")
        self.generate_final_button.clicked.connect(self.generate_final_file)
        self.layout.addWidget(self.generate_final_button)

        self.setLayout(self.layout)

        # 初始化文件路径列表
        self.frame_file_paths = []
        self.point_file_paths = []

    def select_frame_files(self):
        file_paths, _ = QFileDialog.getOpenFileNames(self, "选择画面表", "", "Excel文件 (*.xlsx)")
        if file_paths:
            self.frame_file_paths.extend(file_paths)
            for file_path in file_paths:
                self.update_selected_files_display("画面表", file_path)

    def select_point_files(self):
        file_paths, _ = QFileDialog.getOpenFileNames(self, "选择点位表", "", "Excel文件 (*.xlsx)")
        if file_paths:
            self.point_file_paths.extend(file_paths)
            for file_path in file_paths:
                self.update_selected_files_display("点位表", file_path)

    def cancel_selection(self):
        self.frame_file_paths = []
        self.point_file_paths = []
        self.selected_files_display.clear()

    def update_selected_files_display(self, label, file_path):
        self.selected_files_display.addItem(f"{label}: {os.path.basename(file_path)}")

    def generate_final_file(self):
        if not self.frame_file_paths or not self.point_file_paths:
            QMessageBox.warning(self, "警告", "请选择画面表和点位表")
            return

        try:
            # 创建进度条
            progress_dialog = QProgressDialog("正在生成最终文件...", "取消", 0, 100, self)
            progress_dialog.setWindowModality(Qt.WindowModal)
            progress_dialog.setWindowTitle("生成中")
            progress_dialog.setFixedSize(400, 100)  # 设置固定大小
            progress_dialog.setFont(self.font())  # 使用默认字体
            progress_dialog.show()

            # 读取所有画面表
            frame_dfs = []
            for i, file_path in enumerate(self.frame_file_paths):
                progress_dialog.setValue(int(10 + (i / len(self.frame_file_paths)) * 40))
                frame_df = pd.read_excel(file_path)
                frame_df = frame_df.iloc[:, [1, 5, 6, 7]]  # 提取第2, 6, 7, 8列
                frame_df.columns = ["GraphicID", "Group", "Point", "Name"]
                frame_df["FrameFileName"] = os.path.basename(file_path)  # 添加画面表文件名列
                frame_dfs.append(frame_df)
            frame_df = pd.concat(frame_dfs, ignore_index=True)
            print("画面表列名:", frame_df.columns.tolist())
            progress_dialog.setValue(50)

            # 读取所有点位表
            point_dfs = []
            for i, file_path in enumerate(self.point_file_paths):
                progress_dialog.setValue(int(50 + (i / len(self.point_file_paths)) * 20))
                point_df = pd.read_excel(file_path)
                point_df = point_df.iloc[:, [12, 13, 16]]  # 提取第13, 14, 17列
                point_df.columns = ["Group", "Point", "Name"]
                point_dfs.append(point_df)
            point_df = pd.concat(point_dfs, ignore_index=True)
            print("点位表列名:", point_df.columns.tolist())
            progress_dialog.setValue(70)

            # 匹配数据
            merged_df = pd.merge(frame_df, point_df, left_on=["Group", "Point"], right_on=["Group", "Point"], suffixes=('', '_y'))
            print("合并后的列名:", merged_df.columns.tolist())
            progress_dialog.setValue(80)

            # 保留需要的列
            merged_df = merged_df[["GraphicID", "Group", "Point", "Name", "FrameFileName"]]
            print("保留列后的列名:", merged_df.columns.tolist())
            progress_dialog.setValue(90)

            # 处理col2可能有多个匹配的情况，并去重
            merged_df = merged_df.groupby(["Group", "Point", "Name", "FrameFileName"])["GraphicID"].apply(
                lambda x: ', '.join(set(x))).reset_index()
            print("去重后的列名:", merged_df.columns.tolist())
            progress_dialog.setValue(95)

            # 计算新列的值
            merged_df["min_value"] = merged_df["GraphicID"].apply(
                lambda x: min([val.strip() for val in x.split(',') if val.strip().isdigit()], key=lambda v: int(v)))
            merged_df["MinID"] = "GRA-" + merged_df["min_value"] + "-200"
            print("计算新列后的列名:", merged_df.columns.tolist())
            progress_dialog.setValue(98)

            # 添加空列
            merged_df["Empty01"] = ""
            merged_df["Empty02"] = ""
            merged_df["Empty03"] = ""
            merged_df["Empty04"] = ""
            merged_df["Empty05"] = ""
            merged_df["Empty06"] = ""
            merged_df["Empty07"] = ""

            # 调整列顺序
            merged_df = merged_df[
                ["Group", "Empty01", "Point", "Name", "Empty02", "Empty03", "Empty04", "MinID", "Empty05", "Empty06",
                 "Empty07", "GraphicID", "FrameFileName"]]

            # 保存最终文件
            save_path, _ = QFileDialog.getSaveFileName(self, "保存最终文件", "", "Excel文件 (*.xlsx)")
            if save_path:
                save_progress_dialog = QProgressDialog("正在保存文件...", "取消", 0, 100, self)
                save_progress_dialog.setWindowModality(Qt.WindowModal)
                save_progress_dialog.setWindowTitle("保存中")
                save_progress_dialog.setFixedSize(400, 100)  # 设置固定大小
                save_progress_dialog.setFont(self.font())  # 使用默认字体
                save_progress_dialog.show()

                # 模拟保存文件的过程
                for i in range(101):
                    save_progress_dialog.setValue(i)
                    time.sleep(0.01)  # 模拟耗时操作

                # 定义列标题
                header = ["Group", "", "Point", "Name", "", "", "", "MinID", "", "", "", "GraphicID", "FrameFileName"]

                # 保存文件时指定列标题
                merged_df.to_excel(save_path, index=False, header=header)
                save_progress_dialog.setValue(100)
                QMessageBox.information(self, "成功", f"最终文件已生成并保存到 {save_path}")

            progress_dialog.setValue(100)
        except Exception as e:
            QMessageBox.critical(self, "错误", f"生成最终文件时发生错误: {str(e)}")

if __name__ == "__main__":
    app = QApplication(sys.argv)
    mainWindow = MainWindow()
    mainWindow.show()
    sys.exit(app.exec_())
